#region (c) 2014 THN Solutions LLC. - All rights reserved
/*
THN DATA ABSTRACTION LAYER version 1

Copyright (c) 2014, THN Solutions LLC. ( www.ThnSolutions.com )
Author: Nguyễn, M. Hải                 ( www.Minh-Hai.com     )
All rights reserved.

This library is dual-licensed.
  + For commercial software, please obtain a commercial license from THN Solutions LLC.

  + For free software, this library is licensed under GPL version 3. A summary of GPLv3 is
    listed below. You should also find a copy of GPLv3 as file License_GPLv3.txt included
	with the source-code files.

    This program is free software: you can redistribute it and/or modify
    it under the terms of the GNU General Public License version 3 as published by
    the Free Software Foundation  of the License.

    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License
    along with this program.  If not, see <http://www.gnu.org/licenses/>.
*/
#endregion

#region Using directives
using System;
using Thn.Data.Mapping;
using Thn.Data.Exceptions;
using System.Data;
using Thn.Data.Query;
using System.Collections;
using System.Text;
using Thn.Data.Settings;
#endregion

namespace Thn.Data.Database.Sql.MySql
{
    /// <summary>
    /// Adapter for MySql database
    /// </summary>
    public class MySqlDatabase : AbstractSqlDatabase
    {

        #region Constructors
        /// <summary>
        /// Default constructor
        /// </summary>
        public MySqlDatabase()
        {
            this.Dialect = new MySqlDialect();
            //Since MySQL uses connection pooling,
            //there is no need to keep connection open
            AlwaysCloseConnection = true;
        }
        #endregion

        #region I/O

        #region Refresh
        /// <summary>
        /// Refresh/load data, if neccessary
        /// </summary>
        public override void Refresh()
        {
        }
        #endregion

        #region Flush
        /// <summary>
        /// Persist changes to database, if still buffered
        /// </summary>
        public override void Flush()
        {
        }
        #endregion

        #endregion

        #region On Insert Object
        /// <summary>
        /// Raised after insert command has been built, this method will execute the command.
        /// <para>Override this method for acquiring auto-generated IDs, etc.</para>
        /// </summary>
        /// <param name="value">the data object to be inserted</param>
        /// <param name="command">the prepared database command</param>
        /// <param name="entity">mapping of object to insert</param>
        /// <param name="attributes">mapping of attributes</param>
        /// <param name="hasAuto">Whether identifier of object is auto-generated field</param>
        protected override void OnInsertObject(object value, IDbCommand command, ObjectMap entity, AttributeMap[] attributes, bool hasAuto)
        {
            if (hasAuto)
            {//override to retrieve ID of the object inserted and put it back to the object

                //find the first identifier
                AttributeMap id = null;
                for (int i = 0; i < attributes.Length; i++)
                {
                    if (attributes[i].IsIdentifier)
                    {
                        id = attributes[i];
                        break;
                    }
                }

                //throw exception if could not find any identifier
                if (id == null) NoIdentifierException.Publish(entity.ObjectEntity);

                //build sql string & append to command
                command.CommandText += "SELECT LAST_INSERT_ID() FROM " + entity.DataEntity+";";

                //execute query
                object idValue = ExecuteScalar(command, false);

                //put the new id back to the object
                if (idValue != null)
                {
                    int newID = Convert.ToInt32(idValue);
                    Mapper.SetObjectAttributeValue(value, id, newID);
                }
            }
            else base.OnInsertObject(value, command, entity, attributes, hasAuto);
            
        }
        #endregion

        #region Execute Delete query
        /// <summary>
        /// Process a delete query command
        /// </summary>
        /// <param name="command">The command to process</param>

        protected override void Execute(DeleteQuery command)
        {
            MySqlDialectStrings sql=new MySqlDialectStrings();
            //pre-condition            
            if ((command.DeleteClause == null) && (command.FromClause == null))
            {
                if (!mSuppressException)
                {
                    //DataExceptionBase error = DataExceptionBase.Create(DataExceptionConstants.NoValueInQuery, typeof(QuerySyntaxException), null, null);
                    //if (DataTierSettings.AutoPublishError) Log.Error(error);
                    //if (!DataTierSettings.SilentError) throw error;
                    NoValueInQueryException.Publish();
                }
            }

            //If objects to be delete has been specified, delete them by identifiers
            if (command.DeleteClause != null)
            {
                //objects to delete has been specified
                IList values = (IList)command.DeleteClause;

                DeleteObjects(values);
            }
            else
            {//Objeccts to be delete has not been specified, delete them by condition
                ObjectMap entity = mMapper.GetEntity((Type)command.FromClause);
                StringBuilder buffer = new StringBuilder();
                Thn.Collections.ArrayList valueStack = new Thn.Collections.ArrayList();

                //build query
                BuildDeleteRecordClause(entity, command.WhereClause, buffer, valueStack);

                //build command
                IDbCommand cmd = Dialect.CreateCommand();
                cmd.CommandText = buffer.ToString();

                //append parameter
                for (int m = 0; m < valueStack.Count; m++)
                {
                    IDataParameter parameter = Dialect.CreateParameter(this.Dialect.SqlStrings.ParameterStart + "con" + m.ToString(), valueStack[m].GetType());
                    parameter.Value = valueStack[m];
                    cmd.Parameters.Add(parameter);
                }
                //execute query
                ExecuteNonQuery(cmd, true);
            }


        }
        #endregion

        #region Select
        /// <summary>
        /// Get object(s) matching the condition
        /// </summary>
        /// <param name="objectType">Type of object to query against</param>
        /// <param name="condition">Query criteria. If null, select all objects</param>
        /// <returns>A collection is always returned. If there are no object matches the condition, the collection is empty</returns>
        public override IList SelectObjects(Type objectType, Condition condition)
        {
            //MySqlDialectStrings sql = new MySqlDialectStrings();
            IList result = null;
            Thn.Collections.ArrayList valueStack = new Thn.Collections.ArrayList();
            StringBuilder buffer = new StringBuilder();
            ObjectMap entity = mMapper.GetEntity(objectType);
            AttributeMap[] attributes = mMapper.GetAttributes(objectType);

            //construct command
            BuildSelectOneEntityClause(entity, attributes, condition, buffer, valueStack, null);
            IDbCommand command = Dialect.CreateCommand();
            command.CommandText = buffer.ToString();

            //append value to condition 
            if (condition != null)
            {
                for (int i = 0; i < valueStack.Count; i++)
                {
                    IDataParameter parameter = Dialect.CreateParameter(this.Dialect.SqlStrings.ParameterStart+ "con" + i.ToString(), valueStack[i].GetType());
                    parameter.Value = valueStack[i];
                    command.Parameters.Add(parameter);
                }
            }

            //execute query
            result = ExecuteReaderOneEntity(entity, attributes, command, true);

            return result;
        }
        #endregion

        #region Query Execution
        /// <summary>
        /// Execute reader command for one entity type only.
        /// </summary>
        /// <param name="entity">mapping of object entity</param>
        /// <param name="attributes">attributes which will be read</param>
        /// <param name="command">data command to execute</param>
        /// <param name="disposeCommand">dispose the command object after finish reading</param>
        /// <returns>Always return a collection. Length is zero if no records found</returns>
        protected new IList ExecuteReaderOneEntity(ObjectMap entity, AttributeMap[] attributes, IDbCommand command, bool disposeCommand)
        {
            IList result = null;

            if (ThreadSafe)
            {
                Exception ex = null;

                lock (mSyncRoot)
                {
                    //catch exception so that other thread won't be stuck while
                    //this thread crashes
                    try
                    {
                        result = DoExecuteReaderOneEntity(entity, attributes, command, disposeCommand);
                    }
                    catch (Exception e)
                    {
                        ex = e;
                    }
                }

                //rethrow exception if available
                if (ex != null) throw ex;
            }
            else result = DoExecuteReaderOneEntity(entity, attributes, command, disposeCommand);

            return result;
        }

        /// <summary>
        /// The actual method that execute reader command for one entity type only
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="attributes"></param>
        /// <param name="command"></param>
        /// <param name="disposeCommand"></param>
        /// <returns></returns>
        new IList DoExecuteReaderOneEntity(ObjectMap entity, AttributeMap[] attributes, IDbCommand command, bool disposeCommand)
        {
            IList result = null;
            Thn.Collections.ArrayList lstData = new Thn.Collections.ArrayList();
            lstData.ItemType = entity.ObjectEntity;
            result = lstData;

            PrepareConnection();
            command.Connection = this.Connection;
            IDataReader reader = null;

            //#if (DEBUG)
            if (DataTierSettings.LogCommand) LogCommand(command);
            //#endif

            try
            {
                reader = command.ExecuteReader();

                if (reader != null)
                {
                    while (reader.Read())
                    {
                        //construct new instance
                        object obj = Activator.CreateInstance(entity.ObjectEntity);

                        //assign values from database
                        for (int i = 0; i < attributes.Length; i++)
                        {
                            if (attributes[i].ObjectAttributeType == typeof(bool))
                            {
                                object value = reader.GetBoolean(i);
                                if ((value != null) && (value != DBNull.Value))
                                {
                                    mMapper.SetObjectAttributeValue(obj, attributes[i], value);
                                }
                            }
                            else if (attributes[i].ObjectAttributeType == typeof(Guid))
                            {
                                object value = reader.GetGuid(i);
                                if ((value != null) && (value != DBNull.Value))
                                {
                                    mMapper.SetObjectAttributeValue(obj, attributes[i], value);
                                }
                            }
                            else
                            {
                                object value = reader.GetValue(i);
                                if ((value != null) && (value != DBNull.Value))
                                {
                                    mMapper.SetObjectAttributeValue(obj, attributes[i], value);
                                }
                            }
                        }

                        //put to result list
                        result.Add(obj);
                    }
                }//reader != null
            }//execute reader
            catch (Exception error)
            {
                if (!mSuppressException)
                {
                    DataExceptionBase exception = Dialect.ParseException(error);

                    if (DataTierSettings.AutoPublishError)
                    {
                        if (DataTierSettings.LogCommand) LogCommand(command);
                        else Log.Error(exception);
                    }
                    if (!(DataTierSettings.SilentError)) throw exception;
                }
            }
            finally
            {
                if (reader != null) reader.Close();
                if ((command != null) && (disposeCommand)) command.Dispose();
            }

            CleanupConnection();

            return result;
        }
        #endregion

        #region Debug
        private void LogCommand(IDbCommand command)
        {
            if ((command.Parameters != null) && (command.Parameters.Count > 0))
            {
                object logScope = Log.Start(command.CommandText);

                //Log.Debug(command.CommandText);
                if (command.Parameters != null)
                {
                    foreach (IDataParameter parameter in command.Parameters)
                    {
                        if (parameter.Value == null) Log.Debug(string.Format("         Param: {0} - Value: {1} - Type: {2} - Direction: {3}", parameter.ParameterName, "[null]", parameter.DbType, parameter.Direction));
                        else if ((parameter.Value is string) && (((string)parameter.Value).Equals(string.Empty))) Log.Debug(string.Format("         Param: {0} - Value: {1} - Type: {2} - Direction: {3}", parameter.ParameterName, "[empty]", parameter.DbType, parameter.Direction));
                        else Log.Debug(string.Format("         Param: {0} - Value: {1} - Type: {2} - Direction: {3}", parameter.ParameterName, parameter.Value, parameter.DbType, parameter.Direction));
                    }
                }

                Log.End(logScope);
            }
            else Log.Debug(command.CommandText);
        }
        #endregion

        #region Select Single Entity
        /// <summary>
        /// Process select query command for one single entity
        /// </summary>
        /// <param name="entity">Object mapping</param>
        /// <param name="command">query command</param>
        /// <returns>List of result. If no records returned, list is empty</returns>
        protected override ExecuteResult SelectSingleEntity(ObjectMap entity, SelectQuery command)
        {
            Thn.Collections.ArrayList valueStack = new Thn.Collections.ArrayList();
            StringBuilder buffer = new StringBuilder();
            AttributeMap[] attributes = null;

            //get attributes
            if (command.FieldClause == null)
            {
                //get all attributes
                attributes = mMapper.GetAttributes(entity.ObjectEntity);
            }
            else
            {
                attributes = new AttributeMap[command.FieldClause.Count];

                //select only specified attributes
                for (int i = 0; i < command.FieldClause.Count; i++)
                {
                    attributes[i] = mMapper.GetAttribute(entity.ObjectEntity, command.FieldClause[i].ToString());
                }
            }

            //build command
            IDbCommand cmd = Dialect.CreateCommand();
            BuildSelectOneEntityClause(entity, attributes, command.WhereClause, buffer, valueStack, command);
            cmd.CommandText = buffer.ToString();

            //append criteria parameters
            for (int i = 0; i < valueStack.Count; i++)
            {
                IDataParameter parameter = Dialect.CreateParameter(this.Dialect.SqlStrings.ParameterStart + "con" + i.ToString(), valueStack[i].GetType());
                
                parameter.Value = valueStack[i];
                cmd.Parameters.Add(parameter);
            }

            IList result = ExecuteReaderOneEntity(entity, attributes, cmd, true);

            return new ExecuteResult(result);
        }
        #endregion
    }
}

